﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Xml.Serialization;
using System.Threading;

using RightEdge.Common;

using MySql.Data.MySqlClient;

namespace RightEdge_MySQL_Plugin
{
    public class DataStoreLog : PluginLog
    {
        public DataStoreLog(PluginLogOptions po) : base(po) { }

        private bool _log_sql = true;
        public bool LogSQL { set { _log_sql = value; } get { return (_log_sql); } }
        
        public void captureSQL(string p,string s)
        {
            if (!_log_sql) { return; }
            writeMessage(p + " SQL : '" + s + "'");
        }
    }

    public static class SymbolTableName
    {
        private static string SafeSymbolName(string name) { return name.ToLower().Replace('/', '_').Replace('.', '_'); }
        
        public static string Create(string sym, string freq) { return (SafeSymbolName(sym) + "_" + freq + "_minutes"); }
        public static string Create(string sym) { return (SafeSymbolName(sym) + "_ticks"); }
    }

    public class MySQLBarDataAccessor : IDataAccessor<BarData>
    {
        private SymbolFreq _sym = null;
        public SymbolFreq SymbolFreq { set { _sym = value; } get { return _sym; } }

        public string TableName { get { return (_sym != null) ? SymbolTableName.Create(_sym.Symbol.Name, _sym.Frequency.ToString()) : ""; } }

        MySqlConnection _con = null;
        public MySqlConnection Connection { set { _con = value; } get { return _con; } }

        MySQLStorePlugin _p = null;
        public MySQLStorePlugin Parent { set { _p = value; } get { return _p; } }

        private DataStoreLog _log = null;
        public DataStoreLog Log { set { _log = value; } get { return _log; } }

        int _insert_batch_limit = 10000;
        public int SQLInsertBatchLimit { set { _insert_batch_limit = value; } get { return _insert_batch_limit; } }

        bool _update_on_insert = true;
        public bool SQLUpdateOnInsert { set { _update_on_insert = value; } get { return _update_on_insert; } }

        public MySQLBarDataAccessor(MySQLStorePlugin p, SymbolFreq s, DataStoreLog l)
        {
            _p = p;
            _sym = s;
            _log = l;
            _con = _p.createConnection();
            _insert_batch_limit = p.SQLInsertBatchLimit;
            _update_on_insert = p.SQLUpdateOnInsert;
        }

        public long Delete(DateTime start, DateTime end)
        {
            if (_log != null) { _log.captureDebug("Delete() bars : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "'"); }

            string qry = "DELETE FROM " + TableName + "";
            qry += " WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";
            int x;
            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            { x = cmd.ExecuteNonQuery(); }
            return x;
        }
        
        public long GetCount(DateTime start, DateTime end)
        {
            if (_log != null) { _log.captureDebug("GetCount() bars : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "'"); }

            string qry = "SELECT COUNT(*) FROM " + TableName;
            qry += " WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";
            object res_o;
            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            { res_o = cmd.ExecuteScalar(); }

            return ((long)res_o);
        }
        
        public DateTime GetDateTimeAtIndex(long index, out long numSameDatePreceding)
        {
            throw new NotImplementedException("GetDateTimeAtIndex() - TBI");
        }
        public List<BarData> Load(DateTime start, DateTime end, long maxItems, bool loadFromEnd)
        {
            List<BarData> res_list = new List<BarData>();

            if (_log != null) { _log.captureDebug("Load() bars : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "' maxItems='" + maxItems + "' loadFromEnd='" + loadFromEnd + "'"); }

            string subqry = "SELECT PriceDateTime,Open,High,Low,Close,Bid,Ask,Volume";
            subqry += " FROM " + TableName + "";
            subqry += " WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";
            subqry += " ORDER BY PriceDateTime " + (loadFromEnd ? "DESC" : "ASC");
            if (maxItems > 0) { subqry += " LIMIT " + maxItems; }

            //the result must always be in ascending order
            //if loadFromEnd stuff the select query into a subtable and re-sort in ascending order
            string qry = !loadFromEnd ? subqry : "SELECT * FROM (" + subqry + ") AS subtable ORDER BY PriceDateTime ASC";

            MySqlCommand cmd = new MySqlCommand(qry, _con);

            //execute the command
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                    BarData nbar = new BarData();
                    nbar.EmptyBar = false;
                    nbar.OpenInterest = 0;
                    nbar.BarStartTime = reader.GetDateTime(0);
                    nbar.Open = reader.GetDouble(1);
                    nbar.High = reader.GetDouble(2);
                    nbar.Low = reader.GetDouble(3);
                    nbar.Close = reader.GetDouble(4);
                    nbar.Bid = reader.GetDouble(5);
                    nbar.Ask = reader.GetDouble(6);
                    nbar.Volume = reader.GetUInt64(7);

                    res_list.Add(nbar);
            }
            reader.Close();

            return (res_list);
        }
        public long Save(IList<BarData> items)
        {
            if (items.Count == 0) { return 0; }

            if (_log != null) { _log.captureDebug("Save() bars : table='" + TableName + "' item count='" + items.Count + "'"); }

            string qry_head = "INSERT INTO " + TableName + " (PriceDateTime,Open,High,Low,Close,Bid,Ask,Volume) VALUES ";
            int r, n = 0;
            int x = 0;

            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = _con;

                string qry = "";
                foreach (BarData bd in items)
                {
                    if (n == 0) { qry = qry_head; }
                    else { qry += ","; }
                    n++;

                    qry += "('" + bd.BarStartTime.ToString("s").Replace('T', ' ') + "'," + bd.Open + "," + bd.High + "," + bd.Low + "," + bd.Close + "," + bd.Bid + "," + bd.Ask + "," + bd.Volume + ")";

                    if (n >= _insert_batch_limit)
                    {
                        if (_update_on_insert) { qry += " ON DUPLICATE KEY UPDATE Open=VALUES(Open),High=VALUES(High),Low=VALUES(Low),Close=VALUES(Close),Bid=VALUES(Bid),Ask=VALUES(Ask),Volume=VALUES(Volume)"; }
                        
                        if (_log != null) { _log.captureSQL("Save() bars", qry); }

                        cmd.CommandText = qry;
                        r = cmd.ExecuteNonQuery();
                        if (r != n && _log != null) { _log.captureDebug("Warning, affected rows (" + r + ")does not match row count (" + n + ") in query."); }
                        x += r;
                        n = 0;
                    }
                }
                
                if (n != 0)
                {
                    if (_update_on_insert) { qry += " ON DUPLICATE KEY UPDATE Open=VALUES(Open),High=VALUES(High),Low=VALUES(Low),Close=VALUES(Close),Bid=VALUES(Bid),Ask=VALUES(Ask),Volume=VALUES(Volume)"; }
                    
                    if (_log != null) { _log.captureSQL("Save() bars", qry); }
                    
                    cmd.CommandText = qry;
                    r = cmd.ExecuteNonQuery();
                    if (r != n && _log != null) { _log.captureDebug("Warning, affected rows (" + r + ")does not match row count (" + n + ") in query."); }
                    x += r;
                }
            }
            
            return x;
        }

        public void Dispose()
        {
            if (_con != null) { _con.Dispose(); _con = null; }
        }

        public void VerifyTable()
        {
            if (_log != null) { _log.captureDebug("VerifyTable() : table='" + TableName + "'"); }

            if (Parent.ValidatedTables.Contains(TableName)) { return; }

            string qry = "SELECT count(*) FROM information_schema.tables WHERE table_name = '" + TableName + "' AND table_schema = '" + _con.Database + "'";

            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            {
                long x = (long)cmd.ExecuteScalar();
                if (x == 0)
                {//if x indicates no table exists then create it
                    if (_log != null) { _log.captureDebug("    table not found, attempting CREATE TABLE"); }

                    qry = "CREATE TABLE " + TableName;
                    qry += "( PriceDateTime datetime NOT NULL, Open double NOT NULL, High double NOT NULL,";
                    qry += " Low double NOT NULL, Close double NOT NULL, Bid double NOT NULL,";
                    qry += " Ask double NOT NULL, Volume bigint(20) unsigned NOT NULL, PRIMARY KEY (PriceDateTime) )";
                    cmd.CommandText = qry;
                    if (cmd.ExecuteNonQuery() != 0)
                    {
                        throw new Exception("Problem executing SQL CREATE TABLE '" + TableName + "' command.");
                    }
                }
                else if (x != 1)
                {//if x indicates an error then throw it
                    throw new Exception("Problem executing SQL SELECT information_schema.tables command for table '" + TableName + "'.");
                }
            }

            Parent.ValidatedTables.Add(TableName);
        }
    }

    public class MySQLTickDataAccessor : IDataAccessor<TickData>
    {
        private Symbol _sym = null;
        public Symbol Symbol { set { _sym = value; } get { return _sym; } }

        public string TableName { get { return (_sym != null) ? SymbolTableName.Create(_sym.Name) : ""; } }

        MySqlConnection _con = null;
        public MySqlConnection Connection { set { _con = value; } get { return _con; } }

        MySQLStorePlugin _p = null;
        public MySQLStorePlugin Parent { set { _p = value; } get { return _p; } }

        private DataStoreLog _log = null;
        public DataStoreLog Log { set { _log = value; } get { return _log; } }

        int _insert_batch_limit = 10000;
        public int SQLInsertBatchLimit { set { _insert_batch_limit = value; } get { return _insert_batch_limit; } }

        bool _update_on_insert = true;
        public bool SQLUpdateOnInsert { set { _update_on_insert = value; } get { return _update_on_insert; } }

        public MySQLTickDataAccessor(MySQLStorePlugin p, Symbol s, DataStoreLog l)
        {
            _p = p;
            _sym = s;
            _log = l;
            _con = p.createConnection();
            _insert_batch_limit = p.SQLInsertBatchLimit;
            _update_on_insert = p.SQLUpdateOnInsert;
        }
        
        public long Delete(DateTime start, DateTime end)
        {
            if (_log != null) { _log.captureDebug("Delete() ticks : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "'"); }

            string qry = "DELETE FROM " + TableName + "";
            qry += " USE INDEX(PriceDateTime) WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";

            if (_log != null) { _log.captureSQL("Delete() ticks",qry); }
            int x;
            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            { x = cmd.ExecuteNonQuery(); }
            return x;
        }
        public long GetCount(DateTime start, DateTime end)
        {
            if (_log != null) { _log.captureDebug("GetCount() ticks : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "'"); }

            string qry = "SELECT COUNT(*) FROM " + TableName;
            qry += " USE INDEX(PriceDateTime) WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";

            if (_log != null) { _log.captureSQL("GetCount() ticks",qry ); }

            object res_o;

            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            {
                res_o = cmd.ExecuteScalar();
            }
            if (_log != null) { _log.captureDebug("GetCount() ticks : counted '" + (long)res_o + "' ticks"); }

            return ((long)res_o);
        }
        public DateTime GetDateTimeAtIndex(long index, out long numSameDatePreceding)
        {
            if (_log != null) { _log.captureDebug("GetDateTimeAtIndex() ticks : index='" + index + "'"); }

            string qry = "SELECT PriceDateTime FROM " + TableName;
            qry += " USE INDEX(PriceDateTime) WHERE id = " + (index + 1).ToString();

            if (_log != null) { _log.captureSQL("GetDateTimeAtIndex() ticks",qry); }

            object res_o;
            using(MySqlCommand cmd = new MySqlCommand(qry, _con))
            {res_o = cmd.ExecuteScalar();}

            numSameDatePreceding = 0;//<--- ? what is this value for??

            return ((DateTime)res_o);
        }
        public List<TickData> Load(DateTime start, DateTime end, long maxItems, bool loadFromEnd)
        {
            List<TickData> res_list = new List<TickData>();

            if (_log != null) { _log.captureDebug("Load() ticks : table='" + TableName + "' start='" + start.ToString() + "' end='" + end.ToString() + "' maxItems='" + maxItems + "' loadFromEnd='" + loadFromEnd + "'"); }

            string subqry = "SELECT ID,PriceDateTime,Price,Size,TickType";
            
            subqry += " FROM " + TableName;
            if (maxItems <= 0) { subqry += " USE INDEX(PriceDateTime)"; }//for non-LIMIT queries, force the use of the PriceDateTime key

            subqry += " WHERE PriceDateTime >= '" + start.ToString("s").Replace('T', ' ') + "' AND PriceDateTime <= '" + end.ToString("s").Replace('T', ' ') + "'";
            subqry += " ORDER BY ID " + (loadFromEnd ? "DESC" : "ASC");
            
            if (maxItems > 0) { subqry += " LIMIT " + maxItems; }

            //the result must always be in ascending order
            //if loadFromEnd stuff the select query into a subtable and re-sort in ascending order
            string qry = !loadFromEnd ? subqry : "SELECT * FROM (" + subqry + ") AS subtable ORDER BY ID ASC";

            if (_log != null) { _log.captureSQL("Load() ticks",qry ); }

            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        TickData nbar = new TickData();

                        //column 0 is the id value...
                        nbar.time = reader.GetDateTime(1);//datetime
                        nbar.price = reader.GetDouble(2);//double
                        nbar.size = reader.GetUInt64(3);//ulong
                        nbar.tickType = (TickType)reader.GetByte(4);//byte

                        res_list.Add(nbar);
                    }
                    reader.Close();
                }
            }
            return (res_list);
        }
        public long Save(IList<TickData> items)
        {
            if (items.Count == 0) { return 0; }

            if (_log != null) { _log.captureDebug("Save() ticks : table='" + TableName + "' item count='" + items.Count + "'"); }

            int r, n = 0;
            int x = 0;
            
            string qry_head = "INSERT INTO " + TableName + " ( PriceDateTime,Price,Size,TickType ) VALUES ";
            using (MySqlCommand cmd = new MySqlCommand())
            {
                cmd.Connection = _con;

                string qry = "";
                foreach (TickData td in items)
                {
                    if (n == 0) { qry = qry_head; }
                    else { qry += ","; }
                    n++;
                    
                    qry += "('" + td.time.ToString("s").Replace('T', ' ') + "'," + td.price + "," + td.size + "," + ((byte)td.tickType).ToString() + ")";

                    if (n >= _insert_batch_limit)
                    {
                        if (_update_on_insert) { qry += " ON DUPLICATE KEY UPDATE Price=VALUES(Price),Size=VALUES(Size),TickType=VALUES(TickType)"; }

                        if (_log != null) { _log.captureSQL("Save() ticks", qry); }

                        cmd.CommandText = qry;
                        r = cmd.ExecuteNonQuery();
                        if (r != n && _log != null) { _log.captureDebug("Warning, affected rows (" + r + ")does not match row count (" + n + ") in query."); }
                        x += r;
                        n = 0;
                    }
                }

                if (n != 0)
                {
                    if (_update_on_insert) { qry += " ON DUPLICATE KEY UPDATE Price=VALUES(Price),Size=VALUES(Size),TickType=VALUES(TickType)"; }

                    if (_log != null) { _log.captureSQL("Save() ticks", qry); }

                    cmd.CommandText = qry;
                    r = cmd.ExecuteNonQuery();
                    if (r != n && _log != null) { _log.captureDebug("Warning, affected rows (" + r + ")does not match row count (" + n + ") in query."); }
                    x += r;
                }
            }
            
            return x;
        }

        public void Dispose()
        {
            if (_con != null) { _con.Dispose(); _con = null; }
        }

        public void VerifyTable()
        {
            if (_log != null) { _log.captureDebug("VerifyTable() : table='" + TableName + "'"); }

            if (Parent.ValidatedTables.Contains(TableName)) { return; }

            string qry = "SELECT count(*) FROM information_schema.tables WHERE table_name = '" + TableName + "' AND table_schema = '" + _con.Database + "'";

            if (_log != null) { _log.captureSQL("VerifyTable() ticks",qry); }

            using (MySqlCommand cmd = new MySqlCommand(qry, _con))
            {
                long x = (long)cmd.ExecuteScalar();
                if (x == 0)
                {//if x indicates no table exists then create it
                    if (_log != null) { _log.captureDebug("    table not found, attempting CREATE TABLE"); }

                    qry = "CREATE TABLE " + TableName;
                    qry += "( ID bigint UNSIGNED NOT NULL AUTO_INCREMENT, PriceDateTime datetime NOT NULL, Price double NOT NULL, Size bigint(20) unsigned NOT NULL,";
                    qry += " TickType tinyint NOT NULL,  PRIMARY KEY (ID), KEY (PriceDateTime) )";
                    cmd.CommandText = qry;

                    if (_log != null) { _log.captureSQL("VerifyTable() ticks", qry); }

                    if (cmd.ExecuteNonQuery() != 0)
                    {
                        throw new Exception("Problem executing SQL CREATE TABLE '" + TableName + "' command.");
                    }
                }
                else if (x != 1)
                {//if x indicates an error then throw it
                    throw new Exception("Problem executing SQL SELECT information_schema.tables command for table '" + TableName + "'.");
                }
            }
            Parent.ValidatedTables.Add(TableName);
        }
    }

    public class MySQLStorePlugin : IDataStore
    {
        public MySQLStorePlugin()
        {
            setVersion();
        }
        ~MySQLStorePlugin()
        {
            if (_con != null && _con.State != ConnectionState.Closed)
            {
                _con.Close();
                _con.Dispose();
            }
            if (_log != null)
            {
                _log.closeLog();
                _log = null;
            }
        }

        string _version = null;
        [Category("Version"), Description("The version of the mysql plugin."),ReadOnly(true)]
        public string Version { set { setVersion(); } get { return _version; } }

        private void setVersion()
        {
            System.Reflection.Assembly a = System.Reflection.Assembly.GetAssembly(typeof(MySQLStorePlugin));
            if (a == null) { _version = "error"; return; }
            System.Reflection.AssemblyName an = a.GetName();
            if (an == null || an.Version == null) { _version = "error"; return; }
            _version = an.Version.ToString();
        }

        string _host = "localhost";
        [Category("Server"), Description("The host name of the mysql server")]
        public string Host { set { _host = value; } get { return _host; } }

        int _port = 3306;
        [Category("Server"), Description("The port number of the mysql server")]
        public int Port { set { _port = value; } get { return _port; } }

        string _user = "";
        [Category("Server"), Description("The user name to use when connecting")]
        public string User { set { _user = value; } get { return _user; } }

        bool _prompt_pw = false;
        //[Category("MySQL"), Description("Prompt for a password at connetion time if the password option is empty")]
        //public bool PromptForPassword { set { _prompt_pw = value; } get { return _prompt_pw; } }

        string _pw = "";
        [Category("Server"), Description("The password to use when connecting.")]
        [XmlIgnore,PasswordPropertyText(true)]
        public string Password
        {
            set { _pw = value; }
            get { return _pw; }
        }
        [XmlAttribute("Password"), Browsable(false)]
        public string XMLPassword
        {
            set { _pw = PWSec.Decrypt(value); }
            get { return PWSec.Encrypt(_pw); }
        }

        string _db = "forexdata";
        [Category("Server"), Description("The name of the database schema.")]
        public string Database { set { _db = value; } get { return _db; } }

        PluginLogOptions _log_opts = new PluginLogOptions();
        [Category("Logging"), Description("The logging options.")]
        public PluginLogOptions LogOptions { set { _log_opts = value; } get { return _log_opts; } }


        int _insert_batch_limit = 10000;
        [Category("SQL"), Description("Limit batched SQL INSERT's row counts. No single INSERT statement will include more rows than this limit. Additional rows will be sent in a separate query.")]
        public int SQLInsertBatchLimit { set { _insert_batch_limit = value; } get { return _insert_batch_limit; } }

        bool _update_on_insert = true;
        [Category("SQL"), Description("If a record already exists during an INSERT, should the existing values be updated instead?")]
        public bool SQLUpdateOnInsert { set { _update_on_insert = value; } get { return _update_on_insert; } }

        MySqlConnection _con = null;
        DataStoreLog _log = null;

        private List<string> _validated_tables = new List<string>();
        [Browsable(false)]
        public List<string> ValidatedTables { set { _validated_tables = value; } get { return _validated_tables; } }

        internal MySqlConnection createConnection()
        {
            string pw = _pw;
            if (_prompt_pw && string.IsNullOrEmpty(_pw))
            {
                //prompt for pw
            }

            string c_str = "Database=" + _db + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _user + ";Password=" + pw;

            MySqlConnection con = new MySqlConnection(c_str);
            con.Open();
            return con;
        }

        public IDataAccessor<TickData> GetTickStorage(Symbol sym)
        {
            if (_log == null) { _log = new DataStoreLog(_log_opts); }
            
            MySQLTickDataAccessor ta = new MySQLTickDataAccessor(this, sym, _log);
            ta.VerifyTable();
            return (ta);
        }
        public IDataAccessor<BarData> GetBarStorage(SymbolFreq sym)
        {
            if (_log == null) { _log = new DataStoreLog(_log_opts); }

            MySQLBarDataAccessor da = new MySQLBarDataAccessor(this, sym, _log);
            da.VerifyTable();
            return (da);
        }
        public void FlushAll()
        {
            return;
        }
    }

    public class MySQLFeedPlugin : IService, ITickRetrieval
    {
        public MySQLFeedPlugin()
        { }

        string _log_fname = "C:\\mysql-feed.log";
        PluginLog _log = null;

        public bool BrokerFunctionsAvailable { get { return false; } }
        public bool BarDataAvailable { get { return false; } }
        public bool TickDataAvailable { get { return true; } }
        public bool RealTimeDataAvailable { get { return true; } }

        public bool SupportsMultipleInstances() { return true; }
        
        public ITickRetrieval GetTickDataInterface() { return this; }
        public IBroker GetBrokerInterface() { return null; }
        public IBarDataRetrieval GetBarDataInterface() { return null; }
        public IService GetService() { return this; }

        public bool NeedsAuthentication() { return true; }
        public bool NeedsPort() { return true; }
        public bool NeedsServerAddress() { return true; }

        private string _uname;
        public string UserName { get { return _uname; } set { _uname = value; } }
        
        private string _pw;
        public string Password { get { return _pw; } set { _pw = value; } }

        private string _srvaddr;
        public string ServerAddress { get { return _srvaddr; } set { _srvaddr = value; } }

        private int _port;
        public int Port { get { return _port; } set { _port = value; } }

        #region service description and error members
        public string ServiceName() { return "MySQL Data Feed"; }
        public string id() { return "{43302542-129E-48ac-B440-71A181107126}"; }
        public string Version() { return "0.1a"; }

        public string Author() { return "Mark Loftis"; }
        public string CompanyName() { return ""; }
        public string Description() { return "A data feed emulator which polls a MySQL DB for 'live' data."; }

        private string _error_msg = null;
        public void ClearError() { _error_msg = null; }
        public void SetError(string m) { _error_msg = m; }
        public string GetError() { return _error_msg; }
        #endregion

        #region custom settings
        string _db = "forexdata";
        [Category("MySQL"), Description("The name of the database schema.")]
        public string Database { set { _db = value; } get { return _db; } }

        public bool HasCustomSettings() { return false; }

        public bool ShowCustomSettingsForm(ref SerializableDictionary<string, string> settings)
        {
            throw new NotImplementedException("ShowCustomSettingsForm");
        }
        public bool Initialize(SerializableDictionary<string, string> settings)
        {
            foreach (string k in settings.Keys)
            {
                string v = settings[k];
            }
            return true;
        }
        #endregion

        private MySqlConnection _con = null;
        private object _con_lock = new object();
        private FeedThread _feed_thread = null;

        public bool Connect(ServiceConnectOptions connectOptions)
        {
            lock (_con_lock)
            {
                if (_log == null)
                {
                    _log = new PluginLog();
                    _log.FileName = _log_fname;
                    _log.openLog();
                }
                if (_con != null)
                {
                    throw new NotSupportedException("Cannot call Connect() on an existing connection. Call Disconnect() first.");
                }
                string c_str = "Database=" + _db + ";Data Source=" + _srvaddr + ";Port=" + _port + ";User Id=" + _uname + ";Password=" + _pw;
                try
                {
                    _con = new MySqlConnection(c_str);
                    _con.Open();
                }
                catch (Exception e)
                {
                    SetError("Problem opening MySQL connection : " + e.Message);
                    return false;
                }
            }

            return true;
        }

        public bool Disconnect()
        {
            lock (_con_lock)
            {
                if (_con == null)
                {
                    throw new NotSupportedException("Cannot call Disconnect() on a non-existant connection. Call Connect() first.");
                }
                if (_log != null)
                {
                    _log.closeLog();
                    _log = null;
                }
                if (_con.State != ConnectionState.Closed) { _con.Close(); }
                _con.Dispose();
                _con = null;
            }
            return true;
        }


        private GotTickData _gtd_event;
        public GotTickData TickListener { set { _gtd_event = value; } }
        internal void FireTickEvent(Symbol sym, TickData tick)
        {
            if (_log != null) { _log.captureDebug("Symbol '" + sym.Name + "' Tick : " + tick.time.ToString() + " type='" + tick.tickType + "' size='" + tick.size + "' price='" + tick.price + "'"); }
            _gtd_event.Invoke(sym, tick);
        }

        private bool _is_watching = false;
        public bool IsWatching(){return _is_watching;}
        public bool StartWatching()
        {
            if (_feed_thread == null)
            {
                _feed_thread = new FeedThread(this, _con, _con_lock);
                _feed_thread.Start();
                _is_watching = true;
            }
            return true;
        }
        public bool StopWatching()
        {
            if (_feed_thread != null)
            {
                _feed_thread.Stop();
                _feed_thread = null;
                _is_watching = false;
            }

            return true;
        }

        public bool SetWatchedSymbols(List<Symbol> symbols)
        {
            _feed_thread.UpdateWatchlist(symbols);
            return true;
        }

        /*
        public List<BarData> RetrieveData(Symbol symbol, int frequency, DateTime startDate, DateTime endDate, BarConstructionType barConstruction)
        {
            List<BarData> res = new List<BarData>();
            string tbl = MySQLBarDataAccessor.GetTableName(symbol.Name, frequency.ToString());
            
            string sql = "SELECT PriceDateTime,Open,High,Low,Close,Volume,Bid,Ask FROM " + tbl + " WHERE PriceDateTime >= startDate AND PriceDateTime <= endDate ORDER BY PriceDateTime DESC";
            try
            {
                lock (_con_lock)
                {
                    MySqlCommand cmd = new MySqlCommand(sql, _con);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        BarData bd = new BarData();
                        //setup bd from the reader row
                        bd.EmptyBar = false;
                        bd.OpenInterest = 0;

                        bd.PriceDateTime = reader.GetDateTime(0);
                        bd.Open = reader.GetDouble(1);
                        bd.High = reader.GetDouble(2);
                        bd.Low = reader.GetDouble(3);
                        bd.Close = reader.GetDouble(4);
                        bd.Volume = reader.GetUInt64(5);
                        bd.Bid = reader.GetDouble(6);
                        bd.Ask = reader.GetDouble(7);

                        res.Add(bd);
                    }
                    reader.Dispose();
                    cmd.Dispose();
                }
            }
            catch (Exception e)
            {
                SetError("Problem with RetrieveData() SQl query : " + e.Message);
                return null;
            }

            return res;
        }
        */

        public void Dispose()
        { }
    }

    public class FeedSymbol
    {
        public FeedSymbol(Symbol s, DateTime t) { _sym = s; _last_tick = t; }

        private Symbol _sym;
        public Symbol Symbol { set { _sym = value; } get { return _sym; } }

        private DateTime _last_tick;
        public DateTime LastTickTime { set { _last_tick = value; } get { return _last_tick; } }
    }

    public class FeedThread
    {
        private MySQLFeedPlugin _feed;
        private MySqlConnection _con;
        private object _con_lock;
        private Thread _thread;
        
        private string _error;
        public string Error { get { return _error; } }
        public bool HasError { get { return (!string.IsNullOrEmpty(_error)); } }

        private List<FeedSymbol> _watched_symbols = new List<FeedSymbol>();
        private object _watchlist_lock = new object();

        public FeedThread(MySQLFeedPlugin feed, MySqlConnection con, object con_lock)
        {
            _feed = feed;
            _con = con;
            _con_lock = con_lock;
            _thread = new Thread(new ThreadStart(main));
            _thread.Name = "FeedThread";
        }

        public void Start()
        {
            _thread.Start();
        }
        
        public void Stop()
        {
            _thread.Abort();
        }

        public void UpdateWatchlist(List<Symbol> symlist)
        {
            lock (_watchlist_lock)//lock the watchlist *first*
            {
                List<FeedSymbol> prevlist = _watched_symbols;
                _watched_symbols = new List<FeedSymbol>();

                foreach (Symbol s in symlist)
                {
                    DateTime last_tick=DateTime.MinValue;
                    bool found = false;
                    foreach (FeedSymbol pfs in prevlist)
                    {//find the last time for s in _prev_watchlist
                        if (pfs.Symbol == s) { last_tick = pfs.LastTickTime; found = true; break; }
                    }
                    if (!found)
                    {//if not found try sql select
                        string tname = SymbolTableName.Create(s.Name);
                        string cstr = "SELECT PriceDateTime,ID FROM " + tname + " ORDER BY ID DESC LIMIT 1";
                        lock (_con_lock)//*then* lock the connection
                        {
                            try
                            {
                                using (MySqlCommand cmd = new MySqlCommand(cstr, _con))
                                { last_tick = (DateTime)cmd.ExecuteScalar(); }
                            }
                            catch (Exception e) { _error = e.Message; }
                        }
                    }
                    _watched_symbols.Add(new FeedSymbol(s, last_tick));
                }
            }
        }

        private int _poll_interval = 200;//in miliseconds
        private int _max_symbols_per_poll = 0;//0 for unlimited
        private void main()
        {
            try
            {
                int interval_count=-1;//first pre-increment will take the count to 0
                do
                {
                    int sym_set_count = 0;
                    lock (_watchlist_lock)//lock the watchlist *first*
                    {
                        sym_set_count = _watched_symbols.Count;
                    }
                    int polling_intervals = _max_symbols_per_poll != 0 ? sym_set_count / _max_symbols_per_poll : 1;
                    
                    int sleep_time = _poll_interval / polling_intervals;
                    if (sleep_time == 0) { sleep_time = 1; }
                    
                    Thread.Sleep(sleep_time);//sleep for the polling interval...

                    if (++interval_count >= polling_intervals) { interval_count = 0; }//reset to 0 since the increment has already occured

                    lock (_watchlist_lock)//lock the watchlist *first*
                    {
                        lock (_con_lock)//*then* lock the connection
                        {
                            int si = interval_count * sym_set_count;
                            int max_si = si + ((_max_symbols_per_poll != 0) ? _max_symbols_per_poll : (_watched_symbols.Count - si));
                            for (; si < _watched_symbols.Count && si < max_si; si++)
                            {//call sql select on the tick table
                                FeedSymbol sym=_watched_symbols[si];
                                string tname = SymbolTableName.Create(sym.Symbol.Name);

                                string cstr = "SELECT ID,PriceDateTime,Price,Size,TickType FROM " + tname + " WHERE PriceDateTime > '" + sym.LastTickTime.ToString("s").Replace('T', ' ') + "' ORDER BY ID ASC";
                                try
                                {
                                    using (MySqlCommand cmd = new MySqlCommand(cstr, _con))
                                    {
                                        using (MySqlDataReader reader = cmd.ExecuteReader())
                                        {
                                            DateTime last_time = sym.LastTickTime;
                                            while (reader.Read())
                                            {//foreach record found create a tickdata record and call feed.FireTickEvent()
                                                TickData td = new TickData();

                                                td.time = reader.GetDateTime(1);
                                                td.price = reader.GetDouble(2);
                                                td.size = reader.GetUInt64(3);
                                                byte b = (byte)reader.GetByte(4);
                                                td.tickType = (TickType)b;

                                                last_time = td.time;
                                                _feed.FireTickEvent(sym.Symbol, td);
                                            }
                                            sym.LastTickTime = last_time;

                                            reader.Close();
                                        }
                                    }
                                }
                                catch (Exception e) { _error = e.Message; }
                            }
                        }
                    }
                } while (true);
            }
            catch (ThreadAbortException)
            { }
            catch (Exception e)
            {
                _error = e.Message;
            }
        }
    }
}
